package br.com.fatecpg.ads.les.projeto;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

public class conexao {
    private static Connection con;
    private static PreparedStatement ps;
    
    public static Connection getConnection() throws Exception {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        String url = "jdbc:oracle:thin:@localhost:1521:XE";
        return DriverManager.getConnection(url, "admin", "admin");
    }

    public static int executeStatement(String SQL) throws Exception {
        Exception e = null;
        int result = 0;
        Statement stmt = null;
        try {
            con = getConnection();
            stmt = con.createStatement();
            result = stmt.executeUpdate(SQL);

        } catch (Exception ex) {
            e = ex;
        } finally {
            try {
                stmt.close();
            } catch (Exception ex2) {
            }
            try {
                con.close();
            } catch (Exception ex2) {
            }

        }
        if (e != null) {
            throw e;
        }
        return result;
    }
    
    public static PreparedStatement getPreparedStatement(String SQL){
        ps = null;
        try {
            con = getConnection();
            ps = con.prepareStatement(SQL);
        } catch (Exception ex) {
            Logger.getLogger(conexao.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ps;
    }
    
    public static int executePreparedStatement(PreparedStatement stmt) throws Exception {
        Exception e = null;
        int result = 0;
        try {
            result = stmt.executeUpdate();

        } catch (Exception ex) {
            e = ex;
        } finally {
            try {
                stmt.close();
            } catch (Exception ex2) {
            }
            try {
                con.close();
            } catch (Exception ex2) {
            }

        }
        if (e != null) {
            throw e;
        }
        return result;
    }

    public static ArrayList<Object[]> getQuery(String SQL) throws Exception {
        Exception e = null;
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<Object[]> result = null;
        try {
            con = getConnection();
            stmt = con.createStatement();
            rs = stmt.executeQuery(SQL);
            result = new ArrayList<Object[]>();
            while (rs.next()) {
                Object[] reg = new Object[rs.getMetaData().getColumnCount()];
                for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                    reg[i] = rs.getObject(i + 1);
                }
                result.add(reg);
            }
        } catch (Exception ex) {
            e = ex;

        } finally {
            try {rs.close();} catch (Exception ex2) {}
            try {stmt.close();} catch (Exception ex2) {}
            try {con.close();} catch (Exception ex2) {}

        }
        if (e != null) {
            throw e;
        }
        return result;
    }
}
//Fim da conexão
//Modelo baseado nesse site > http://www.freelancersbrasil.com/classe-conexao-jsp-mysql/